﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;



namespace DBLib_WFF
{
    public class PrcParam
    {
        public int type = 0;
        public string name = "";
        public int lenght = 0;
        public byte xprec = 0;
        public byte xscale = 0;
        public int isoutparam = 0;
        public object value = null;
    }
    public class DBProcedures
    {
        private static int TimeOutSec = 300;

        #region Common functions

        public static int execProcedure(string connectKey, string procedure, SortedList paramList, ref DataTable dt, ref string msg)
        {
            int rtn = -1;
            dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection conn = DBFactory.getConnect(connectKey);
            SqlCommand sqlCmd = new SqlCommand(procedure, conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandTimeout = 1800;
            try
            {
                for (int i = 0; i < paramList.Count; i++)
                {
                    PrcParam param = (PrcParam)paramList.GetByIndex(i);
                    switch (param.type)
                    {
                        case 34:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Image, param.lenght);
                            break;
                        case 48:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.TinyInt, param.lenght);
                            break;
                        case 52:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallInt, param.lenght);
                            break;
                        case 56:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Int, param.lenght);
                            break;
                        case 98:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 106:
                        case 108:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Decimal, param.lenght);
                            sqlCmd.Parameters[param.name].Precision = param.xprec;
                            sqlCmd.Parameters[param.name].Scale = param.xscale;
                            break;
                        case 127:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.BigInt, param.lenght);
                            break;
                        case 165:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarBinary, param.lenght);
                            break;
                        case 167:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 175:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Char, param.lenght);
                            break;
                        case 231:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NVarChar, param.lenght / 2);
                            break;
                        case 239:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NChar, param.lenght / 2);
                            break;
                        case 61:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.DateTime, param.lenght);
                            break;
                        case 58:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallDateTime, param.lenght);
                            break;
                        case 189:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Timestamp, param.lenght);
                            break;
                        case 60:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Money, param.lenght);
                            break;
                        case 122:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallMoney, param.lenght);
                            break;
                    }
                    if (param.isoutparam == 0)
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Input;
                    else
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Output;
                    sqlCmd.Parameters[param.name].Value = param.value;
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                adapter.SelectCommand = sqlCmd;
                adapter.Fill(dt);
                dt.TableName = "table_name";
                rtn = 0;
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Execute procedure failed(" + procedure + "). Error messsage : " + ex.Message);
                msg = ex.Message;
                rtn = -1;
                return rtn;
            }
            finally
            {
                adapter.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int execProcedure(string procedure, SortedList paramList, ref DataTable dt, ref string msg)
        {
            return execProcedure("Default", procedure, paramList, ref dt, ref msg);
        }

        public static int execProcedure(string connectKey, string procedure, SortedList paramList, AsyncCallback callback, ref string msg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect(connectKey);
            SqlCommand sqlCmd = new SqlCommand(procedure, conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandTimeout = 1800;
            try
            {
                for (int i = 0; i < paramList.Count; i++)
                {
                    PrcParam param = (PrcParam)paramList.GetByIndex(i);
                    switch (param.type)
                    {
                        case 34:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Image, param.lenght);
                            break;
                        case 48:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.TinyInt, param.lenght);
                            break;
                        case 52:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallInt, param.lenght);
                            break;
                        case 56:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Int, param.lenght);
                            break;
                        case 98:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 106:
                        case 108:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Decimal, param.lenght);
                            sqlCmd.Parameters[param.name].Precision = param.xprec;
                            sqlCmd.Parameters[param.name].Scale = param.xscale;
                            break;
                        case 127:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.BigInt, param.lenght);
                            break;
                        case 165:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarBinary, param.lenght);
                            break;
                        case 167:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 175:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Char, param.lenght);
                            break;
                        case 231:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NVarChar, param.lenght / 2);
                            break;
                        case 239:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NChar, param.lenght / 2);
                            break;
                        case 61:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.DateTime, param.lenght);
                            break;
                        case 58:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallDateTime, param.lenght);
                            break;
                        case 189:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Timestamp, param.lenght);
                            break;
                        case 60:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Money, param.lenght);
                            break;
                        case 122:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallMoney, param.lenght);
                            break;
                    }
                    if (param.isoutparam == 0)
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Input;
                    else
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Output;
                    sqlCmd.Parameters[param.name].Value = param.value;
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.BeginExecuteNonQuery(callback, sqlCmd);
                rtn = 0;
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Execute procedure failed(" + procedure + "). Error messsage : " + ex.Message);
                msg = ex.Message;
                rtn = -1;
                return rtn;
            }
            finally
            {
                sqlCmd = null;
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int execProcedure(string procedure, SortedList paramList, AsyncCallback callback, ref string msg)
        {
            return execProcedure("Default", procedure, paramList, callback, ref msg);
        }

        #endregion
    }
}
